package cn.rengy.tool.excel;

import java.io.IOException;

public class ExcelUtil {
    public static void main(String[] args) throws IOException {

//        String sql=FileUtils.readFileToString(new File("C:\\Users\\ren_g\\Desktop\\data2.sql"),"utf-8");
//
//        for(int i=1;i<101;i++){
//            System.out.println(i+"="+columnNumberToName(i).toLowerCase());
//            sql=sql.replaceFirst("data"+i,columnNumberToName(i).toLowerCase());
//        }
//        FileUtils.writeStringToFile(new File("C:\\Users\\ren_g\\Desktop\\data2.sql"),sql,"utf-8");
        for(int i=99;i<150;i++){
            String s="`"+columnNumberToName(i).toLowerCase()+"` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '"+columnNumberToName(i)+"',";
            System.out.println(s);
        }

        for(int i=50;i<150;i++){
            System.out.println(columnNumberToName(i).toLowerCase());
        }
//        String a=null;
//        String b=null;
//        System.out.println(a==b);
//        System.out.println(Objects.equals(a,b));
//        System.out.println(Character.isLetter('A'));
//        System.out.println(Character.isLetter('Ａ'));
//        System.out.println(Character.getType('A'));
        ;
    }

    /**
     * 单元格名称转换行列索引
     * @param cellName
     * @return
     */
    public static CellPosition getCellPosition(String cellName){
        if(cellName==null){
            return null;
        }
        CellPosition cellPosition=null;
        char[] statement = cellName.toCharArray();
        int i = 0;
        while (i < statement.length) {
            char c = statement[i];
            //Character.UPPERCASE_LETTER Character.LOWERCASE_LETTER
            //DECIMAL_DIGIT_NUMBER 数字，包括全角
            if(Character.getType(c)==Character.DECIMAL_DIGIT_NUMBER){//数字
                cellPosition=new CellPosition();
                cellPosition.setCellNumber(columnNameToNumber(cellName.substring(0,i)));
                cellPosition.setRowNumber(Integer.parseInt(cellName.substring(i)));
                break;
            }
            i++;
        }
        //int cellIndex=columnNameToNumber(columnName);
        return cellPosition;
    }
    /**
     * 列名数字转字母
     * @param n
     * @return
     */
    public static String columnNumberToName(int n) {
        if (n < 1) {
            return "A";
        }
        StringBuilder sb = new StringBuilder();
        while (n > 0) {
            n--;
            sb.append((char) (n % 26 + 'A'));
            n = n / 26;
        }
        return sb.reverse().toString();
    }

    /**
     * 列名字母转数字
     * @param columnName
     * @return
     */
    public static int columnNameToNumber(String columnName) {
        int sum = 0;
        if (columnName==null||columnName.length()==0) {
            return sum;
        }
        columnName=columnName.toUpperCase();
        for (int i = 0; i < columnName.length(); i++) {
            sum *= 26;
            sum += (columnName.charAt(i) - 'A' + 1);
        }
        return sum;
    }

}
